﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Configuration;
using Epam.PhotoGallery.Interfaces.Providers;
using Epam.PhotoGallery.Interfaces.DataTransferObjects;

namespace Epam.PhotoGallery.Bussines.Providers
{
    public class OfficeLibraryDataProvider : ILibraryDataProvider
    {
        #region Constants

        private const string CONNECTION_STRING_NAME = "OfficeLibrary";

        #endregion

        #region Fields

        private string _connectionString;

        #endregion


        public OfficeLibraryDataProvider()
        {
            this._connectionString = ConfigurationManager.ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
        }

        /// <summary>
        /// Gets books from office library.
        /// </summary>
        /// <param name="rootPath">The root path.</param>
        /// <returns>
        /// Return IEnumerable<LibraryBook>.
        /// </returns>
        public IEnumerable<Book> GetAllBooksFromLibrary()
        {
            const string GET_BOOKS_TEXT_COMMAND = "SELECT * FROM [Sheet1$]";
            const string ORDER_ID_COLUMN_NAME = "#";
            const string BOOK_COLUMN_NAME = "Book";
            const string OWNER_COLUMN_NAME = "Owner";
            const string CURRENT_READER_COLUMN_NAME = "Current reader";
            const string START_DATE_COLUMN_NAME = "Start date";
            var allBooks = new List<Book>();

            using (OleDbConnection connection = new OleDbConnection(this._connectionString))
            {
                try
                {
                    connection.Open();
                    using (OleDbCommand getAllBooksCommand = new OleDbCommand())
                    {                                                                //TODO: Exception Handling!
                        getAllBooksCommand.CommandText = GET_BOOKS_TEXT_COMMAND;
                        getAllBooksCommand.Connection = connection;

                        using (OleDbDataReader booksReader = getAllBooksCommand.ExecuteReader())
                        {
                            while (booksReader.Read())
                            {
                                Book currentBook = new Book();

                                currentBook.ID = booksReader[ORDER_ID_COLUMN_NAME].ToString().Replace("-", string.Empty);
                                currentBook.FullName = booksReader[BOOK_COLUMN_NAME].ToString();
                                currentBook.Owner =booksReader[OWNER_COLUMN_NAME].ToString();
                                currentBook.CurrentReader = booksReader[CURRENT_READER_COLUMN_NAME].ToString();
                                currentBook.StartDate=booksReader[START_DATE_COLUMN_NAME] as DateTime?;

                                allBooks.Add(currentBook);
                            }
                        }

                    }

                }
                catch (OleDbException ex)
                {
                    
                
                }
            }

            return allBooks;
        }
    }
}
